using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
/// <summary>
/// Summary description for processProduct
/// </summary>
public class processProduct:DBConnectionTool
{    
	public processProduct() : base()
	{
		//
		// TODO: Add constructor logic here
		//        
	}

    public string geProductID()
    {
        OpenCn();
        SqlCommand cmd = new SqlCommand("select right(max(ProductID),5) from Products", cn);
        String s = cmd.ExecuteScalar().ToString();
        Int32 result;
        if (Int32.TryParse(s, out result))
            result++;
        else
            result = 1;
        int l = result.ToString().Length;
        int count = 5 - l;
        string sresult = "Pd";
        for (int i = 0; i < count; i++)
        {
            sresult = sresult + "0";
        }
        sresult = sresult + result;
        CloseCn();
        return sresult;
    }

    public DataSet getProduct()
    {
        OpenCn();
        SqlDataAdapter da = new SqlDataAdapter("select * from Products", cn);
        DataSet ds = new DataSet("ArtShop");
        da.Fill(ds, "Products");
        CloseCn();
        return ds;
    }
    public DataTable ListProduct()
    {
        OpenCn();
        SqlDataAdapter da = new SqlDataAdapter("select * from Products", cn);
        DataTable dt = new DataTable("Products");
        da.Fill(dt);
        CloseCn();
        return dt;
    }


    public DataSet getNewProduct()
    {
        OpenCn();
        SqlDataAdapter da = new SqlDataAdapter("select top(12) * from Products order by ProductID desc", cn);
        DataSet ds = new DataSet("ArtShop");
        da.Fill(ds, "Products");
        CloseCn();
        return ds;
    }
    //public int getProducts(string ProductTypeID)
    //{
    //    OpenCn();
    //    SqlCommand cmd = new SqlCommand("select * from Products where ProductTypeID=@ProductTypeID", cn);
    //    cmd.Parameters.Add(new SqlParameter("@ProductTypeID", SqlDbType.VarChar, 5));
    //    cmd.Parameters["@ProductTypeID"].Value = ProductTypeID;
    //    int result = cmd.ExecuteNonQuery();
    //    CloseCn();
    //    return result;
    //}
    //public DataSet getProductByType(string ProductTypeID)
    //{
    //    OpenCn();
    //    SqlDataAdapter da = new SqlDataAdapter("select * from Products where ProductTypeID='" + ProductTypeID +"'", cn);
    //    DataSet ds = new DataSet("ArtShop");
    //    da.Fill(ds, "Products");
    //    CloseCn();
    //    return ds;
    //}
    public DataTable getProductByType(string ProductTypeID)
    {
        OpenCn();
        SqlDataAdapter da = new SqlDataAdapter("select * from Products where ProductTypeID='" + ProductTypeID + "'", cn);
        DataTable dt = new DataTable("Products");
        da.Fill(dt);
        CloseCn();
        return dt;
    }
    public DataTable getProductByID(string ProductID)
    {
        OpenCn();
        SqlDataAdapter da = new SqlDataAdapter("select * from Products where ProductID='" + ProductID + "'", cn);
        DataTable dt = new DataTable("Products");
        da.Fill(dt);
        CloseCn();
        return dt;
    }

    public DataTable searchByName(string ProductName)
    {
        OpenCn();
        SqlDataAdapter da = new SqlDataAdapter("select * from Products Where ProductName Like N'%" + ProductName + "%'", cn);
        DataTable dt = new DataTable("Products");
        da.Fill(dt);
        CloseCn();
        return dt;
    }

    public DataTable getTopSeller()
    {
        OpenCn();
        SqlDataAdapter da = new SqlDataAdapter("SELECT TOP 5 ProductID,Sold=COUNT(ProductID), ProductName, ProductImage, ProductPrice, ProductWarranty, ProductQuantity FROM View_Order WHERE OrderStatus = 'Delivered' GROUP BY ProductID, ProductName, ProductImage, ProductPrice, ProductWarranty, ProductQuantity ORDER BY COUNT(ProductID) DESC", cn);
        DataTable dt = new DataTable("View_Order");
        da.Fill(dt);
        CloseCn();
        return dt;
    }



    public int deleteProduct(string ProductID)
    {
        OpenCn();
        SqlCommand cmd = new SqlCommand("Delete Products Where ProductID ='"+ProductID+"'", cn);
        int result = cmd.ExecuteNonQuery();
        CloseCn();
        return result;
    }

    public string getProductNameByID(string ProductID)
    {
        OpenCn();
        SqlCommand cmd = new SqlCommand("select ProductName from Products where ProductID = '"+ProductID+"'");
        string result = cmd.ExecuteScalar().ToString();
        CloseCn();
        return result;
    }

    public int insertProduct(string ProductID, string ProductName, string ProductImage, string ProductDetail, float ProductPrice,string ProductWarranty, int ProductQuantity, string ProductTypeID)
    {
        OpenCn();
        SqlCommand cmd = new SqlCommand("Insert Into Products(ProductID,ProductName,ProductImage,ProductDetail,ProductPrice,ProductWarranty,ProductQuantity,ProductTypeID) Values(@ProductID,@ProductName,@ProductImage,@ProductDetail,@ProductPrice,@ProductWarranty,@ProductQuantity,@ProductTypeID)", cn);
        cmd.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.Char, 7));
        cmd.Parameters.Add(new SqlParameter("@ProductName", SqlDbType.NVarChar, 200));
        cmd.Parameters.Add(new SqlParameter("@ProductImage", SqlDbType.NVarChar, 200));
        cmd.Parameters.Add(new SqlParameter("@ProductDetail", SqlDbType.NVarChar, 200));
        cmd.Parameters.Add(new SqlParameter("@ProductPrice", SqlDbType.Float));
        cmd.Parameters.Add(new SqlParameter("@ProductWarranty", SqlDbType.NVarChar, 200));
        cmd.Parameters.Add(new SqlParameter("@ProductQuantity", SqlDbType.Int));
        cmd.Parameters.Add(new SqlParameter("@ProductTypeID", SqlDbType.Char, 5));        
        cmd.Parameters["@ProductID"].Value = ProductID;
        cmd.Parameters["@ProductName"].Value = ProductName;
        cmd.Parameters["@ProductImage"].Value = ProductImage;
        cmd.Parameters["@ProductDetail"].Value = ProductDetail;
        cmd.Parameters["@ProductPrice"].Value = ProductPrice;
        cmd.Parameters["@ProductWarranty"].Value = ProductWarranty;
        cmd.Parameters["@ProductQuantity"].Value = ProductQuantity;
        cmd.Parameters["@ProductTypeID"].Value = ProductTypeID;        
        int result = cmd.ExecuteNonQuery();
        CloseCn();
        return result;
    }

    public int updateProduct(string ProductID, string ProductName, string ProductImage, string ProductDetail, float ProductPrice, string ProductWarranty, int ProductQuantity, string ProductTypeID)
    {
        OpenCn();
        SqlCommand cmd = new SqlCommand("update Products set ProductName=@ProductName,ProductImage=@ProductImage,ProductDetail=@ProductDetail,ProductPrice=@ProductPrice,ProductWarranty=@ProductWarranty,ProductQuantity=@ProductQuantity,ProductTypeID=@ProductTypeID where ProductID = @ProductID", cn);
        cmd.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.Char, 7));
        cmd.Parameters.Add(new SqlParameter("@ProductName", SqlDbType.NVarChar, 200));
        cmd.Parameters.Add(new SqlParameter("@ProductImage", SqlDbType.NVarChar, 200));
        cmd.Parameters.Add(new SqlParameter("@ProductDetail", SqlDbType.NVarChar, 200));
        cmd.Parameters.Add(new SqlParameter("@ProductPrice", SqlDbType.Float));
        cmd.Parameters.Add(new SqlParameter("@ProductWarranty", SqlDbType.NVarChar, 200));
        cmd.Parameters.Add(new SqlParameter("@ProductQuantity", SqlDbType.Int));
        cmd.Parameters.Add(new SqlParameter("@ProductTypeID", SqlDbType.Char, 5));
        cmd.Parameters["@ProductID"].Value = ProductID;
        cmd.Parameters["@ProductName"].Value = ProductName;
        cmd.Parameters["@ProductImage"].Value = ProductImage;
        cmd.Parameters["@ProductDetail"].Value = ProductDetail;
        cmd.Parameters["@ProductPrice"].Value = ProductPrice;
        cmd.Parameters["@ProductWarranty"].Value = ProductWarranty;
        cmd.Parameters["@ProductQuantity"].Value = ProductQuantity;
        cmd.Parameters["@ProductTypeID"].Value = ProductTypeID;
        int result = cmd.ExecuteNonQuery();
        CloseCn();
        return result;
    }
}
